package bancoperu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import bancoperu.model.Customer;
import bancoperu.model.Account;
import bancoperu.model.Transaction;
import bancoperu.util.Converter;

public class TransactionDAO extends BaseDAO {

	
	
	public Transaction insert(Transaction vo) throws Exception {
		String query = "INSERT INTO Transaction(AccountID, CustomerID, type, TransactionTime, Amount, AccountID2) VALUES (?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		System.out.println("INTO TRANSACTIONDAO!!!!!!!!!!!");
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			System.out.println("111");
			stmt.setString(1, vo.getAccount().getAccountID());
			System.out.println("222");
			stmt.setString(2, vo.getCustomer().getCustomerId());
			System.out.println("333");
			stmt.setInt(3, vo.getType());
			System.out.println("444");
			stmt.setDate(4, Converter.utilDateToSqlDate(vo.getTransaction()));
			System.out.println("55");
			stmt.setDouble(5, vo.getAmount());
			System.out.println("666");
			
			if ( vo.getAccount2() != null) {
				stmt.setString(6, vo.getAccount2().getAccountID());
			} else {
				stmt.setString(6, null);
			}
			
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}			

		} catch (SQLException e) {
			System.out.println("Error en TX: " + e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	
	public Collection<Transaction> getFullTransactions(String AccountID, String CustomerID) throws Exception {
		//(idTransaction, AccountID, CustomerID, type, TransactionTime, Amount, AccountID2
		String query = "SELECT TransactionTime, Type, Amount FROM Transaction WHERE AccountID = ? AND CustomerID = ? ORDER BY TransactionTime";
		Collection<Transaction> lista = new ArrayList<Transaction>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, AccountID);
			stmt.setString(2, CustomerID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				
//				Customer cust = new Customer();
//				cust.setCustomerId(rs.getString("CustomerID"));
//				Account acct = new Account();
//				acct.setAccountID(rs.getString("AccountID"));
				
				Transaction vo = new Transaction();
//				vo.setCustomer(cust);
//				vo.setAccount(acct);
				//vo.setTransaction(Converter.utilDateToSqlDate(rs.getDate("TransactionTime")));
				vo.setTransactionString(Converter.sqlDateToString(rs.getDate("TransactionTime")));
				vo.setType(rs.getInt("Type"));
				vo.setAmount(rs.getDouble("Amount"));
				
				
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}
	
	public Collection<Transaction> getTypedTransactions() throws Exception {
		//(idTransaction, AccountID, CustomerID, type, TransactionTime, Amount, AccountID2
		String query = "SELECT TransactionTime, Amount FROM Transaction WHERE AccountID = ? AND CustomerID = ? AND Type = ? ORDER BY TransactionTime";
		Collection<Transaction> lista = new ArrayList<Transaction>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				
				Customer cust = new Customer();
				cust.setCustomerId(rs.getString("CustomerID"));
				Account acct = new Account();
				acct.setAccountID(rs.getString("AccountID"));
				int Type;
				Type = rs.getInt("Type");
				
				Transaction vo = new Transaction();
				vo.setCustomer(cust);
				vo.setAccount(acct);
				vo.setType(Type);
				vo.setTransaction(Converter.utilDateToSqlDate(rs.getDate("TransactionTime")));
				vo.setAmount(rs.getDouble("Amount"));
				
				
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}
	
	
	
}
	
